import delimited "./DataSkeleton/AssetClasses/AEM_LevFactor.csv", varnames(1) rowrange(4) clear 
gen quarter=yq(real(substr(not,1,4)),real(substr(not,6,1)))
gen aem_old=real(v2)
gen aem_new=real(v3)
format quarter %tq
keep quarter aem*
save "./OutputInterim/aem.dta",replace

*** Update AEM
import excel "./DataSkeleton/AssetClasses/bd_assets_liab.xls", sheet("FRED Graph") cellrange(A12:C314) firstrow clear
gen quarter=qofd(obs)
format quarter %tq
rename BOGZ1FL664090005Q asset
rename BOGZ1FL664190005Q liab
gen lev=asset/(asset-liab)
tsset quarter
gen dlev=100*(log(lev)-log(L.lev))
mmerge quarter using "./OutputInterim/aem.dta"
gen q=quarter(obs)
keep if quarter>=tq(1965q3)
gen dlev_sa=.
levelsof quarter, local(cid)
foreach i of local cid{
gen sample=(quarter<=`i')
bysort sample: egen mean_dlev=mean(dlev) if sample==1
gen res=dlev-mean_dlev
bysort q sample: egen mean_res=mean(res)
replace dlev_sa=dlev-mean_res if quarter==`i'
drop sample mean_dlev res mean_res
}
sort obs
keep quarter lev dlev_sa aem_old aem_new
rename dlev_sa dlev
keep if quarter>=tq(1968q1)
save "./OutputInterim/aem_update.dta",replace
outsheet using "./OutputInterim/aem_update.csv",c replace


import delimited "./DataSkeleton/AssetClasses/He_Kelly_Manela_Factors_quarterly.csv", varnames(1) rowrange(4) clear 
tostring yyyyq, replace
gen quarter=yq(real(substr(yyyyq,1,4)),real(substr(yyyyq,5,1)))
format quarter %tq
rename intermediary_capital_ratio hkm
rename intermediary_capital_risk_factor hkm_factor
rename intermediary_value_weighted_inve int_equity
keep quarter hkm hkm_factor int_equity 
save "./OutputInterim/He_Kelly_Manela_Factors_quarterly.dta",replace

import delimited "./DataSkeleton/AssetClasses/He_Kelly_Manela_Factors_daily.csv", varnames(1) rowrange(4) clear 
tostring yyyymmdd, replace
gen date=date(yyyymmdd,"YMD")
format date %td
rename intermediary_capital_ratio hkm
rename intermediary_capital_risk_factor hkm_factor
rename intermediary_value_weighted_inve int_equity
keep date hkm hkm_factor int_equity 
save "./OutputInterim/He_Kelly_Manela_Factors_daily.dta",replace

